# -*- coding: utf-8 -*-
"""
CPS 基础表统计报表 Provider。
"""
from __future__ import annotations

import json
from datetime import datetime, timedelta
from typing import Any, Dict, List, Optional, Tuple

from app.blueprints.reports.providers.base import BaseProvider, SafeDict
from app.services.report_service import ReportTemplateService
from app.utils.logger import FlinkLogger

# 延迟初始化 logger，避免应用上下文问题
logger = None


def get_logger():
    global logger
    if logger is None:
        logger = FlinkLogger.get_logger(__name__)
    return logger


class CPSBaseStatisticsProvider(BaseProvider):
    """CPS基础表统计报表数据提供者"""

    def _get_platform_configs(self) -> Dict[str, Any]:
        """从数据库 default_params 读取平台配置"""
        if not self.template_code:
            raise ValueError("模板编码不能为空")
        
        try:
            tpl = ReportTemplateService.get_template_by_code(self.template_code)
            if not tpl:
                raise ValueError(f"模板 {self.template_code} 不存在")
            
            default_params = tpl.get("default_params")
            if isinstance(default_params, str):
                try:
                    default_params = json.loads(default_params)
                except Exception as e:
                    get_logger().error(f"解析 default_params 失败: {e}")
                    raise ValueError(f"解析模板配置失败: {e}")
            
            if not isinstance(default_params, dict):
                raise ValueError("default_params 必须是字典类型")
            
            platform_configs = default_params.get("platform_configs", {})
            if not platform_configs:
                raise ValueError("模板配置中缺少 platform_configs")
            
            return platform_configs
        except Exception as e:
            get_logger().error(f"读取平台配置失败: {e}", exc_info=True)
            raise

    def _get_sql_template(self) -> str:
        """从数据库 default_params 读取 SQL 模板"""
        if not self.template_code:
            raise ValueError("模板编码不能为空")
        
        try:
            tpl = ReportTemplateService.get_template_by_code(self.template_code)
            if not tpl:
                raise ValueError(f"模板 {self.template_code} 不存在")
            
            default_params = tpl.get("default_params")
            if isinstance(default_params, str):
                try:
                    default_params = json.loads(default_params)
                except Exception as e:
                    get_logger().error(f"解析 default_params 失败: {e}")
                    raise ValueError(f"解析模板配置失败: {e}")
            
            if not isinstance(default_params, dict):
                raise ValueError("default_params 必须是字典类型")
            
            sql_template = default_params.get("sql_template", "")
            if not sql_template:
                raise ValueError("模板配置中缺少 sql_template")
            
            return sql_template
        except Exception as e:
            get_logger().error(f"读取 SQL 模板失败: {e}", exc_info=True)
            raise

    def _get_field_labels(self) -> Dict[str, str]:
        """获取字段英文名到中文标题的映射：默认值 -> 模板配置 -> 运行时自定义"""
        labels: Dict[str, str] = {}
        
        # 默认字段标签
        default_labels = {
            "data_comment": "数据说明",
            "platform": "平台",
            "base_date_count": "基准日期总量",
            "compare_date_count": "对比日期总量",
            "increment": "增量",
            "growth_rate": "环比增长率",
            "trend": "变化趋势",
        }
        
        # 从模板配置读取
        if self.template_code:
            try:
                tpl = ReportTemplateService.get_template_by_code(self.template_code)
                if tpl:
                    default_params = tpl.get("default_params")
                    if isinstance(default_params, str):
                        try:
                            default_params = json.loads(default_params)
                        except Exception:
                            default_params = {}
                    if isinstance(default_params, dict):
                        template_labels = default_params.get("field_labels", {}) or {}
                        if template_labels:
                            labels = template_labels
            except Exception:
                get_logger().debug(
                    "[CPS基础表统计] 读取模板字段标签失败", exc_info=True
                )

        # 运行时自定义标签（优先级最高）
        runtime_labels = {}
        if isinstance(self.params, dict):
            runtime_labels = self.params.get("field_labels") or {}

        # 合并：默认 -> 模板 -> 运行时
        merged = default_labels.copy()
        if isinstance(labels, dict):
            merged.update(labels)
        if isinstance(runtime_labels, dict):
            merged.update(runtime_labels)

        get_logger().debug(
            "[CPS基础表统计] 字段标签合并结果: %s",
            json.dumps(merged, ensure_ascii=False),
        )
        return merged

    def _build_time_dates(
        self, comparison_type: str, base_date: Optional[str] = None, compare_date: Optional[str] = None
    ) -> Tuple[str, str, str]:
        """根据对比类型计算基准日期和对比日期，并返回日期格式
        
        Returns:
            Tuple[str, str, str]: (base_date_value, compare_date_value, date_format)
                - base_date_value: 基准日期值（格式根据对比类型而定）
                - compare_date_value: 对比日期值（格式根据对比类型而定）
                - date_format: DATE_FORMAT格式字符串 ('%Y-%m-%d', '%Y-%m', '%Y')
        """
        now = datetime.now()
        
        if comparison_type == "day":
            # 昨天 vs 今天，使用日期格式
            base = (now - timedelta(days=1)).strftime("%Y-%m-%d")
            compare = now.strftime("%Y-%m-%d")
            date_format = "%Y-%m-%d"
        elif comparison_type == "yesterday":
            # 前天 vs 昨天，使用日期格式（确保数据已完整）
            base = (now - timedelta(days=2)).strftime("%Y-%m-%d")
            compare = (now - timedelta(days=1)).strftime("%Y-%m-%d")
            date_format = "%Y-%m-%d"
        elif comparison_type == "month":
            # 上月 vs 本月，使用月份格式
            last_month = (now.replace(day=1) - timedelta(days=1))
            base = last_month.strftime("%Y-%m")
            compare = now.strftime("%Y-%m")
            date_format = "%Y-%m"
        elif comparison_type == "year":
            # 去年 vs 今年，使用年份格式
            base = f"{now.year - 1}"
            compare = f"{now.year}"
            date_format = "%Y"
        elif comparison_type == "custom":
            # 使用用户指定的日期，需要判断格式
            if not base_date or not compare_date:
                raise ValueError("自定义模式下必须指定 base_date 和 compare_date")
            
            # 验证日期格式
            self._validate_custom_date_format(base_date, compare_date)
            
            base = base_date
            compare = compare_date
            # 根据输入的日期格式判断类型
            # 如果长度是10（YYYY-MM-DD），则为日期
            # 如果长度是7（YYYY-MM），则为月份
            # 如果长度是4（YYYY），则为年份
            if len(base_date) == 10 and len(compare_date) == 10:
                date_format = "%Y-%m-%d"
            elif len(base_date) == 7 and len(compare_date) == 7:
                date_format = "%Y-%m"
            elif len(base_date) == 4 and len(compare_date) == 4:
                date_format = "%Y"
            else:
                # 默认使用日期格式
                date_format = "%Y-%m-%d"
                get_logger().warning(
                    "[CPS基础表统计] 无法确定自定义日期格式，使用日期格式。base_date=%s, compare_date=%s",
                    base_date, compare_date
                )
        else:
            raise ValueError(f"不支持的对比类型: {comparison_type}")
        
        return base, compare, date_format
    
    def _validate_custom_date_format(self, base_date: str, compare_date: str):
        """验证自定义日期格式
        
        Args:
            base_date: 基准日期字符串
            compare_date: 对比日期字符串
            
        Raises:
            ValueError: 如果日期格式不规范
        """
        import re
        from datetime import datetime
        
        # 验证日期格式（YYYY-MM-DD）
        date_pattern = r'^\d{4}-\d{2}-\d{2}$'
        # 验证月份格式（YYYY-MM）
        month_pattern = r'^\d{4}-\d{2}$'
        # 验证年份格式（YYYY）
        year_pattern = r'^\d{4}$'
        
        # 检查基准日期和对比日期格式是否一致
        base_is_date = bool(re.match(date_pattern, base_date))
        base_is_month = bool(re.match(month_pattern, base_date))
        base_is_year = bool(re.match(year_pattern, base_date))
        
        compare_is_date = bool(re.match(date_pattern, compare_date))
        compare_is_month = bool(re.match(month_pattern, compare_date))
        compare_is_year = bool(re.match(year_pattern, compare_date))
        
        # 确保两个日期格式一致
        if base_is_date and not compare_is_date:
            raise ValueError(f"对比日期格式不一致：基准日期为日期格式（YYYY-MM-DD），但对比日期格式不正确：{compare_date}")
        if base_is_month and not compare_is_month:
            raise ValueError(f"对比日期格式不一致：基准日期为月份格式（YYYY-MM），但对比日期格式不正确：{compare_date}")
        if base_is_year and not compare_is_year:
            raise ValueError(f"对比日期格式不一致：基准日期为年份格式（YYYY），但对比日期格式不正确：{compare_date}")
        
        # 如果都不匹配，说明格式不正确
        if not (base_is_date or base_is_month or base_is_year):
            raise ValueError(f"基准日期格式不正确，应为 YYYY-MM-DD、YYYY-MM 或 YYYY 格式：{base_date}")
        if not (compare_is_date or compare_is_month or compare_is_year):
            raise ValueError(f"对比日期格式不正确，应为 YYYY-MM-DD、YYYY-MM 或 YYYY 格式：{compare_date}")
        
        # 验证日期有效性（仅对日期格式）
        if base_is_date:
            try:
                datetime.strptime(base_date, "%Y-%m-%d")
            except ValueError:
                raise ValueError(f"基准日期无效：{base_date}")
        
        if compare_is_date:
            try:
                datetime.strptime(compare_date, "%Y-%m-%d")
            except ValueError:
                raise ValueError(f"对比日期无效：{compare_date}")
        
        # 验证月份有效性（仅对月份格式）
        if base_is_month:
            try:
                datetime.strptime(base_date, "%Y-%m")
            except ValueError:
                raise ValueError(f"基准月份无效：{base_date}")
        
        if compare_is_month:
            try:
                datetime.strptime(compare_date, "%Y-%m")
            except ValueError:
                raise ValueError(f"对比月份无效：{compare_date}")
        
        # 验证年份有效性（仅对年份格式）
        if base_is_year:
            year = int(base_date)
            if year < 1900 or year > 2100:
                raise ValueError(f"基准年份超出有效范围（1900-2100）：{base_date}")
        
        if compare_is_year:
            year = int(compare_date)
            if year < 1900 or year > 2100:
                raise ValueError(f"对比年份超出有效范围（1900-2100）：{compare_date}")

    def _build_monitor_list_sql(
        self, selected_platforms: List[str], platform_configs: Dict[str, Any]
    ) -> str:
        """根据选择的平台生成 monitor_list SQL"""
        monitor_list_parts = []
        
        # 构建平台映射：platform -> platform_all
        platform_to_all = {}
        for platform_code, config in platform_configs.items():
            if isinstance(config, dict):
                platform_all = config.get("platform_all", platform_code)
                platform_to_all[platform_code] = platform_all
        
        # 按 platform_all 分组平台
        grouped_platforms: Dict[str, List[str]] = {}
        for platform in selected_platforms:
            platform_all = platform_to_all.get(platform, platform)
            if platform_all not in grouped_platforms:
                grouped_platforms[platform_all] = []
            grouped_platforms[platform_all].append(platform)
        
        # 生成 SQL 行：SELECT 'platform_all' AS platform_all, 'platform' AS platform
        for platform_all, platforms in grouped_platforms.items():
            for platform in platforms:
                monitor_list_parts.append(f"    SELECT '{platform_all}' AS platform_all, '{platform}' AS platform")
        
        if not monitor_list_parts:
            return ""
        
        return "\n    UNION ALL\n".join(monitor_list_parts)

    def _render_sql_template(
        self, sql_template: str, context: Dict[str, Any]
    ) -> str:
        """渲染 SQL 模板"""
        try:
            # 使用 SafeDict 确保缺失的占位符返回空字符串
            safe_context = SafeDict(context)
            rendered_sql = sql_template.format_map(safe_context)
            return rendered_sql
        except Exception as e:
            get_logger().error(f"渲染 SQL 模板失败: {e}", exc_info=True)
            raise ValueError(f"渲染 SQL 模板失败: {e}")

    def collect(self) -> Dict[str, Any]:
        """收集数据"""
        try:
            get_logger().info("[CPS基础表统计] 开始收集数据")
            
            # 记录完整的参数结构，用于调试
            get_logger().info(
                "[CPS基础表统计] 接收到的参数: %s",
                json.dumps(self.params, ensure_ascii=False, default=str)
            )
            
            display_fields = self.params.get("display_fields")
            get_logger().info(
                "[CPS基础表统计] display_fields参数值: %s (类型: %s)",
                display_fields,
                type(display_fields).__name__
            )

            data_source_config = self.params.get("data_source_config", {})
            data_source_id = data_source_config.get("data_source_id")

            if not data_source_id:
                raise ValueError("数据源ID不能为空")

            platform_filter = self.params.get("platform_filter", {})
            selected_platforms = platform_filter.get("selected_platforms", ["dy"])

            get_logger().debug(
                "[CPS基础表统计] 平台筛选: selected_platforms=%s",
                selected_platforms,
            )

            time_comparison = self.params.get("time_comparison", {})
            comparison_type = time_comparison.get("comparison_type", "day")
            base_date = time_comparison.get("base_date")
            compare_date = time_comparison.get("compare_date")

            # 计算日期和日期格式
            base_date_str, compare_date_str, date_format = self._build_time_dates(
                comparison_type, base_date, compare_date
            )

            get_logger().debug(
                "[CPS基础表统计] 时间对比: %s vs %s (类型: %s, 格式: %s)",
                base_date_str,
                compare_date_str,
                comparison_type,
                date_format,
            )

            # 从数据库读取平台配置
            platform_configs = self._get_platform_configs()

            # 构建 monitor_list SQL
            monitor_list_sql = self._build_monitor_list_sql(
                selected_platforms, platform_configs
            )

            if not monitor_list_sql:
                get_logger().warning(
                    "[CPS基础表统计] 没有平台数据，selected_platforms=%s",
                    selected_platforms,
                )
                final_sql = (
                    "SELECT '无数据' AS '数据说明', '无平台选择' AS '平台', "
                    "0 AS '基准日期总量', 0 AS '对比日期总量', 0 AS '增量', "
                    "'0%' AS '环比增长率', '无数据' AS '变化趋势'"
                )
            else:
                # 获取字段标签
                field_labels = self._get_field_labels()

                # 获取 SQL 模板
                sql_template = self._get_sql_template()

                # 构建模板上下文
                context = {
                    "monitor_list_sql": monitor_list_sql,
                    "base_date": base_date_str,
                    "compare_date": compare_date_str,
                    "date_format": date_format,
                    "data_comment_label": field_labels.get("data_comment", "数据说明"),
                    "platform_label": field_labels.get("platform", "平台"),
                    "base_date_count_label": field_labels.get("base_date_count", "基准日期总量"),
                    "compare_date_count_label": field_labels.get("compare_date_count", "对比日期总量"),
                    "increment_label": field_labels.get("increment", "增量"),
                    "growth_rate_label": field_labels.get("growth_rate", "环比增长率"),
                    "trend_label": field_labels.get("trend", "变化趋势"),
                }

                # 渲染 SQL 模板
                final_sql = self._render_sql_template(sql_template, context)

            get_logger().info(
                "执行CPS基础表统计查询，平台: %s, 日期对比: %s vs %s",
                selected_platforms,
                base_date_str,
                compare_date_str,
            )

            with self.get_data_source_connection(data_source_id) as conn:
                cursor = conn.cursor()
                cursor.execute(final_sql)
                columns = [desc[0] for desc in cursor.description]
                rows = cursor.fetchall()

                get_logger().info("查询完成，返回 %s 条记录", len(rows))

                rows_list: List[List[Any]] = []
                for row in rows:
                    if isinstance(row, (list, tuple)):
                        rows_list.append(list(row))
                    elif isinstance(row, dict):
                        rows_list.append([row.get(col) for col in columns])
                    else:
                        try:
                            rows_list.append(list(row))
                        except TypeError:
                            rows_list.append([row])

            # 根据 display_fields 过滤列
            original_column_count = len(columns)
            get_logger().info(
                "[CPS基础表统计] 开始字段过滤 - 原始列: %s, display_fields参数: %s",
                columns,
                self.params.get("display_fields")
            )
            
            filtered_columns, filtered_rows = self._filter_display_fields(
                columns, rows_list
            )
            
            # 只有当 filtered_columns 不是 None 时才更新（None 表示不进行过滤）
            # 如果是空列表 []，表示用户取消了所有字段，应该使用空列表
            if filtered_columns is not None:
                get_logger().info(
                    "[CPS基础表统计] 字段过滤完成: 原始列数=%d, 过滤后列数=%d, 过滤后列: %s",
                    original_column_count,
                    len(filtered_columns),
                    filtered_columns
                )
                columns = filtered_columns
                rows_list = filtered_rows
            else:
                get_logger().warning(
                    "[CPS基础表统计] 字段过滤未执行 - filtered_columns为None，将显示所有字段"
                )

            return {
                "query_result": {
                    "columns": columns,
                    "rows": rows_list,
                }
            }

        except Exception as exc:
            get_logger().error("收集CPS基础表统计数据失败: %s", str(exc), exc_info=True)
            raise

    def _filter_display_fields(
        self, columns: List[str], rows: List[List[Any]]
    ) -> Tuple[Optional[List[str]], Optional[List[List[Any]]]]:
        """根据 display_fields 过滤列"""
        if not isinstance(self.params, dict):
            get_logger().debug("[CPS基础表统计] _filter_display_fields - params is not dict")
            return None, None
        
        display_fields = self.params.get("display_fields")
        if not display_fields:
            get_logger().debug("[CPS基础表统计] _filter_display_fields - display_fields is empty or None")
            return None, None

        field_labels = self._get_field_labels()
        column_index_map = {col: idx for idx, col in enumerate(columns)}

        get_logger().info(
            "[CPS基础表统计] _filter_display_fields - columns: %s, display_fields: %s, field_labels: %s",
            columns,
            display_fields,
            field_labels,
        )

        filtered_columns: List[str] = []
        selected_indexes: List[int] = []
        
        for field in display_fields:
            label = field_labels.get(field)
            if not label:
                get_logger().warning(
                    "[CPS基础表统计] _filter_display_fields - field '%s' not found in field_labels",
                    field
                )
                continue
            
            column_idx = column_index_map.get(label)
            if column_idx is None:
                get_logger().warning(
                    "[CPS基础表统计] _filter_display_fields - label '%s' (for field '%s') not found in columns: %s",
                    label,
                    field,
                    columns
                )
                continue
            
            get_logger().debug(
                "[CPS基础表统计] _filter_display_fields - field '%s' -> label '%s' -> column index %d",
                field,
                label,
                column_idx
            )
            filtered_columns.append(label)
            selected_indexes.append(column_idx)

        # 如果 display_fields 不为空，但没有任何字段匹配，返回空列表（表示过滤后没有字段）
        # 如果 display_fields 为空，则在方法开始时已经返回 None, None（表示不进行过滤）
        if not filtered_columns:
            # 如果用户选择了字段（display_fields 不为空），但所有字段都不匹配，返回空列表
            get_logger().warning(
                "[CPS基础表统计] _filter_display_fields - no columns matched from display_fields %s, "
                "available columns: %s, field_labels: %s. Returning empty list.",
                display_fields,
                columns,
                field_labels
            )
            # 返回空列表，表示用户选择了某些字段，但这些字段都不存在或无法匹配
            return [], []

        get_logger().info(
            "[CPS基础表统计] _filter_display_fields - filtered to %d columns (from %d requested): %s",
            len(filtered_columns),
            len(display_fields),
            filtered_columns
        )

        filtered_rows: List[List[Any]] = []
        for row in rows:
            filtered_row = []
            for idx in selected_indexes:
                filtered_row.append(row[idx] if idx < len(row) else None)
            filtered_rows.append(filtered_row)
        
        return filtered_columns, filtered_rows

    def validate_params(self) -> List[str]:
        """验证参数"""
        errors = []

        if "data_source_config" not in self.params:
            errors.append("缺少数据源配置")
        else:
            data_source_config = self.params["data_source_config"]
            if not data_source_config.get("data_source_id"):
                errors.append("数据源ID不能为空")

        platform_filter = self.params.get("platform_filter", {})
        if not platform_filter:
            errors.append("缺少平台筛选配置")
        else:
            selected_platforms = platform_filter.get("selected_platforms", [])
            if not selected_platforms:
                errors.append("至少需要选择一个平台")
            else:
                # 从数据库读取平台配置进行验证
                try:
                    platform_configs = self._get_platform_configs()
                    invalid_platforms = [
                        p for p in selected_platforms if p not in platform_configs
                    ]
                    if invalid_platforms:
                        errors.append(f"无效的平台: {', '.join(invalid_platforms)}")
                except Exception as e:
                    get_logger().warning(f"验证平台配置时出错: {e}")
                    errors.append(f"平台配置验证失败: {str(e)}")

        time_comparison = self.params.get("time_comparison", {})
        if not time_comparison:
            errors.append("缺少时间对比配置")
        else:
            comparison_type = time_comparison.get("comparison_type")
            if not comparison_type:
                errors.append("请选择对比类型")
            elif comparison_type == "custom":
                if not time_comparison.get("base_date") or not time_comparison.get("compare_date"):
                    errors.append("自定义模式下必须指定基准日期和对比日期")

        return errors

    def get_platform_list(self) -> List[Dict[str, str]]:
        """从数据库配置获取平台列表"""
        try:
            platform_configs = self._get_platform_configs()
            return [
                {"value": key, "label": value.get("name", key)}
                for key, value in platform_configs.items()
            ]
        except Exception as e:
            get_logger().error(f"获取平台列表失败: {e}", exc_info=True)
            return []

    def generate_summary(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """生成数据摘要"""
        try:
            query_result = data.get("query_result", {})
            columns = query_result.get("columns", [])
            rows = query_result.get("rows", [])

            if not rows:
                return {
                    "total_records": 0,
                    "platform_count": 0,
                    "avg_growth_rate": 0,
                    "status": "无数据",
                }

            total_records = len(rows)
            platform_count = len({row[1] for row in rows if len(row) > 1})

            growth_rates = []
            for row in rows:
                # 假设环比增长率在第5列（索引4）
                if len(row) > 4 and row[4] and isinstance(row[4], (int, float, str)):
                    try:
                        growth_rate = float(str(row[4]).replace("%", ""))
                        growth_rates.append(growth_rate)
                    except (ValueError, TypeError):
                        continue

            avg_growth_rate = sum(growth_rates) / len(growth_rates) if growth_rates else 0

            return {
                "total_records": total_records,
                "platform_count": platform_count,
                "avg_growth_rate": round(avg_growth_rate, 2),
                "status": "正常",
            }

        except Exception as exc:
            get_logger().error("生成摘要失败: %s", str(exc), exc_info=True)
            return {
                "total_records": 0,
                "platform_count": 0,
                "avg_growth_rate": 0,
                "status": "错误",
            }

    def _build_select_clause_by_display_fields(self, field_labels: Dict[str, str]) -> str:
        """根据 display_fields 构建 SELECT 子句"""
        display_fields = self.params.get("display_fields")
        
        # 定义所有字段的SQL表达式和顺序
        field_definitions = {
            "data_comment": {
                "sql": """CASE
        WHEN m.platform_all IS NULL THEN '全平台汇总'
        WHEN m.platform IS NULL THEN '平台汇总'
        ELSE '子平台'
    END AS `{data_comment_label}`""",
            },
            "platform": {
                "sql": """CASE
        WHEN m.platform_all IS NULL THEN '平台'
        WHEN m.platform IS NULL THEN CASE m.platform_all
            WHEN 'dy'  THEN '抖音'
            WHEN 'tb'  THEN '淘宝'
            WHEN 'jd'  THEN '京东'
            WHEN 'pdd' THEN '拼多多'
            WHEN 'vph' THEN '唯品会'
            ELSE m.platform_all
        END
        ELSE CASE m.platform
            WHEN 'dy'  THEN '抖音好单库'
            WHEN 'dyc' THEN '抖音团长'
            WHEN 'tb'  THEN '淘宝联盟'
            WHEN 'tbc' THEN '淘宝招商团长'
            WHEN 'tbb' THEN '淘宝商单广场'
            WHEN 'jd'  THEN '京东联盟'
            WHEN 'pdd' THEN '多多进宝'
            WHEN 'vph' THEN '唯品会开放平台'
            ELSE m.platform
        END
    END AS `{platform_label}`""",
            },
            "base_date_count": {
                "sql": "COALESCE(SUM(d.base_date_cnt), 0) AS `{base_date_count_label}`",
            },
            "compare_date_count": {
                "sql": "COALESCE(SUM(d.compare_date_cnt), 0) AS `{compare_date_count_label}`",
            },
            "increment": {
                "sql": "COALESCE(SUM(d.compare_date_cnt), 0) - COALESCE(SUM(d.base_date_cnt), 0) AS `{increment_label}`",
            },
            "growth_rate": {
                "sql": """CONCAT(CAST(ROUND(
        CASE
            WHEN COALESCE(SUM(d.base_date_cnt), 0) = 0 THEN 0
            ELSE (COALESCE(SUM(d.compare_date_cnt), 0) - COALESCE(SUM(d.base_date_cnt), 0)) * 100.0 / SUM(d.base_date_cnt)
        END, 2
    ) AS STRING), '%') AS `{growth_rate_label}`""",
            },
            "trend": {
                "sql": """CASE
        WHEN COALESCE(SUM(d.compare_date_cnt), 0) - COALESCE(SUM(d.base_date_cnt), 0) > 0 THEN '增长'
        WHEN COALESCE(SUM(d.compare_date_cnt), 0) - COALESCE(SUM(d.base_date_cnt), 0) < 0 THEN '下降'
        ELSE '持平'
    END AS `{trend_label}`""",
            },
        }
        
        # 如果没有指定display_fields，返回所有字段
        if not display_fields:
            display_fields = list(field_definitions.keys())
        
        # 构建模板上下文（用于替换字段标签占位符）
        label_context = {
            "data_comment_label": field_labels.get("data_comment", "数据说明"),
            "platform_label": field_labels.get("platform", "平台"),
            "base_date_count_label": field_labels.get("base_date_count", "基准日期总量"),
            "compare_date_count_label": field_labels.get("compare_date_count", "对比日期总量"),
            "increment_label": field_labels.get("increment", "增量"),
            "growth_rate_label": field_labels.get("growth_rate", "环比增长率"),
            "trend_label": field_labels.get("trend", "变化趋势"),
        }
        
        # 构建SELECT子句
        select_parts = []
        for field in display_fields:
            if field in field_definitions:
                sql_expr = field_definitions[field]["sql"]
                # 替换字段标签占位符
                sql_expr = sql_expr.format_map(SafeDict(label_context))
                select_parts.append(sql_expr)
        
        # 如果没有选中任何字段，返回空SELECT
        if not select_parts:
            return "1 AS '无选中字段'"
        
        return ",\n\n    ".join(select_parts)

    def preview_sql(self) -> str:
        """生成预览 SQL（根据 display_fields 动态构建 SELECT 子句）"""
        try:
            data_source_config = self.params.get("data_source_config", {})
            data_source_id = data_source_config.get("data_source_id")

            if not data_source_id:
                raise ValueError("数据源ID不能为空")

            platform_filter = self.params.get("platform_filter", {})
            selected_platforms = platform_filter.get("selected_platforms", ["dy"])

            time_comparison = self.params.get("time_comparison", {})
            comparison_type = time_comparison.get("comparison_type", "day")
            base_date = time_comparison.get("base_date")
            compare_date = time_comparison.get("compare_date")

            # 计算日期和日期格式
            base_date_str, compare_date_str, date_format = self._build_time_dates(
                comparison_type, base_date, compare_date
            )

            # 从数据库读取平台配置
            platform_configs = self._get_platform_configs()

            # 构建 monitor_list SQL
            monitor_list_sql = self._build_monitor_list_sql(
                selected_platforms, platform_configs
            )

            if not monitor_list_sql:
                return (
                    "SELECT '无数据' AS '数据说明', '无平台选择' AS '平台', "
                    "0 AS '基准日期总量', 0 AS '对比日期总量', 0 AS '增量', "
                    "'0%' AS '环比增长率', '无数据' AS '变化趋势'"
                )

            # 获取字段标签
            field_labels = self._get_field_labels()

            # 根据 display_fields 构建 SELECT 子句
            select_clause = self._build_select_clause_by_display_fields(field_labels)

            # 构建完整的SQL模板
            sql_template = f"""/*
 * 报表名称: CPS基础表统计报表
 * 描述: 统计各平台在两个时间点的商品增量情况
 * 功能:
 *   1. 统计基准日期各平台的商品总数
 *   2. 统计对比日期各平台的商品总数
 *   3. 计算增量(对比日期总量 - 基准日期总量)
 *   4. 计算环比增长率((对比日期-基准日期)/基准日期 * 100%)
 *   5. 按platform_all分组汇总,支持展开查看子平台明细
 * 数据说明:
 *   - 数据源: dws.dws_cps_product_base_info
 *   - 统计维度: platform_all(汇总平台), platform(子平台)
 *   - 时间范围: 基准日期 vs 对比日期
 */

WITH monitor_list AS (
    -- 1. 定义监控列表（确保这些平台即使无数据也能显示）
{{monitor_list_sql}}
),
real_data AS (
    -- 2. 统计实际数据（只查有数据的部分）
    SELECT
        platform,
        SUM(CASE WHEN DATE_FORMAT(etl_date,'{{date_format}}') = '{{base_date}}' THEN 1 ELSE 0 END) AS base_date_cnt,
        SUM(CASE WHEN DATE_FORMAT(etl_date,'{{date_format}}') = '{{compare_date}}' THEN 1 ELSE 0 END) AS compare_date_cnt
    FROM dws.dws_cps_product_base_info
    WHERE DATE_FORMAT(etl_date,'{{date_format}}') IN ('{{base_date}}', '{{compare_date}}')
    GROUP BY platform
)
SELECT
    {select_clause}

FROM monitor_list m
LEFT JOIN real_data d ON m.platform = d.platform

-- 使用 ROLLUP 自动生成汇总行
GROUP BY ROLLUP(m.platform_all, m.platform)

-- 排序逻辑，使每个平台汇总后紧跟其明细
ORDER BY
    -- 1. 首先按平台分组（全平台汇总排最前）
    CASE WHEN m.platform_all IS NULL THEN '' ELSE m.platform_all END,
    -- 2. 确保平台汇总行显示在明细之前
    CASE WHEN m.platform IS NULL THEN 0 ELSE 1 END,
    -- 3. 最后按平台代码排序
    m.platform"""

            # 构建模板上下文
            context = {
                "monitor_list_sql": monitor_list_sql,
                "base_date": base_date_str,
                "compare_date": compare_date_str,
                "date_format": date_format,
                "data_comment_label": field_labels.get("data_comment", "数据说明"),
                "platform_label": field_labels.get("platform", "平台"),
                "base_date_count_label": field_labels.get("base_date_count", "基准日期总量"),
                "compare_date_count_label": field_labels.get("compare_date_count", "对比日期总量"),
                "increment_label": field_labels.get("increment", "增量"),
                "growth_rate_label": field_labels.get("growth_rate", "环比增长率"),
                "trend_label": field_labels.get("trend", "变化趋势"),
            }

            # 渲染 SQL 模板
            final_sql = self._render_sql_template(sql_template, context)
            return final_sql

        except Exception as exc:
            get_logger().error("生成预览SQL失败: %s", str(exc), exc_info=True)
            raise

